﻿using EmailClient.Model;
using ServiceStack.OrmLite;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EmailClient.ViewModel
{
    class MailFolderViewModel
    {
        public static async Task<ObservableCollection<MailFolder>> GetFolders()
        {
            var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
            using (var db = dbFactory.Open())
            {
                return new ObservableCollection<MailFolder>(await db.SelectAsync<MailFolder>());
            }
        }

        public static async Task<ObservableCollection<MailFolder>> GetFolders(int accountId)
        {
            var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
            using (var db = dbFactory.Open())
            {
                return new ObservableCollection<MailFolder>(await db.SelectAsync<MailFolder>(x => x.AccountId== accountId));
            }
        }

        // Summary:
        //     Getting mail folder.
        //
        // Parameters:
        //   accountId:
        //     Id an email acount.
        //   name:
        //      Name of folder
        //
        // Returns:
        //     Returns mail folder.
        //
        // Exceptions:
        //
        public static async Task<MailFolder> GetFolder(int accountId, string name)
        {
            var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
            using (var db = dbFactory.Open())
            {
                var l = new List<MailFolder>(await db.SelectAsync<MailFolder>(x => x.Name == name && x.AccountId == accountId));
                return l.First<MailFolder>();
            }
        }

        // Summary:
        //     Getting mail folder.
        //
        // Parameters:
        //   accountId:
        //     Id an email acount.
        //   folderId:
        //      id of folder
        //
        // Returns:
        //     Returns mail folder.
        //
        // Exceptions:
        //
        public static async Task<MailFolder> GetFolder(int accountId, int folderId)
        {
            var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
            using (var db = dbFactory.Open())
            {
                var l = new List<MailFolder>(await db.SelectAsync<MailFolder>(x => x.Id == folderId && x.AccountId == accountId));
                return l.First<MailFolder>();
            }
        }

        // Summary:
        //     Getting mail folder Id.
        //
        // Parameters:
        //   statusId:
        //     Status of the letter.
        //   accountId:
        //      Id of the email account
        //
        // Returns:
        //     Returns mail folder Id.
        //
        // Exceptions:
        //
        public static async Task<int> GetFolderId(int statusId, int accountId)
        {
            var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
            using (var db = dbFactory.Open())
            {
                var l = new List<MailFolder>(await db.SelectAsync<MailFolder>(x => x.StatusId == statusId && x.AccountId == accountId)); 
                return l.First<MailFolder>().Id;
            }
        }

        // Summary:
        //     Getting mail folder Id.
        //
        // Parameters:
        //   name:
        //     Name of the folder.
        //   accountId:
        //      Id of the email account
        //
        // Returns:
        //     Returns mail folder Id.
        //
        // Exceptions:
        //
        public static async Task<int> GetFolderId(string name, int accountId)
        {
            var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
            using (var db = dbFactory.Open())
            {
                var l = new List<MailFolder>(await db.SelectAsync<MailFolder>(x => x.Name == name.Trim() && x.AccountId == accountId));
                return l.First<MailFolder>().Id;
            }
        }

        
        // Summary:
        //     Getting status Id use folder Id.
        //
        // Parameters:
        //   folderId:
        //     Id a folder of the email account.
        //   accountId:
        //      Id of the email account
        //
        // Returns:
        //     Returns status Id.
        //
        // Exceptions:
        //
        public static async Task<string> InsertUpdate(MailFolder item)
        {
            var result = "success";
            try
            {
                var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
                using (var db = dbFactory.Open())
                {
                    var isFolder = await db.SelectAsync<MailFolder>(x => x.Name == item.Name && x.AccountId == item.AccountId);
                    if (isFolder.Count > 0) await db.UpdateAsync(item);
                    else
                    {
                        await db.InsertAsync(item);
                        item.Id = await GetFolderId(item.Name, item.AccountId);
                    }
                }
            }
            catch (Exception e) { result = e.Message; }

            return result;
        }

        public static async Task<int> GetStatusId(int folderId, int accountId)
        {
            var dbFactory = new OrmLiteConnectionFactory(App.DBPath, SqliteDialect.Provider, false);
            using (var db = dbFactory.Open())
            {
                var l = new List<MailFolder>(await db.SelectAsync<MailFolder>(x => x.Id == folderId && x.AccountId == accountId));
                return l.First<MailFolder>().StatusId;
            }
        }       
                
    }
}
